package com.sduept.nwld.primaryequipment.rest;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.sduept.bigdata.fault.breaker.service.BreakerActionQueryManager;
import com.sduept.bigdata.fault.dto.BreakerActionInfoDTO;
import com.sduept.core.dao.SuperDAO;
import lombok.extern.slf4j.Slf4j;
import net.sf.json.JSONArray;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
@Slf4j
@RestController
@RequestMapping("/pms/")
public class BreakerDataServices {
	@Autowired
	private SuperDAO dao;
	@Autowired
	private BreakerActionQueryManager query;

	public BreakerDataServices() {

	}

	/**
	 * 根据名称name获取设备缺陷信息
	 * 
	 * @param name
	 * @return
	 */

	@GetMapping("/getPmsDefectsByname/{name}/{option}")
	public String getPmsDefects(@PathVariable("name") String name,
			@PathVariable("option") String option) {

		try {
			String sql = "";
			String namestr = "";
			JSONObject jsonObject = JSON.parseObject(option);
			JSONObject devicetype = (JSONObject) jsonObject.get("devicetype");
			JSONObject datetimeJson = (JSONObject) jsonObject.get("datetime");
			String starttime = (String) datetimeJson.get("starttime");
			String endtime = (String) datetimeJson.get("endtime");
			namestr = URLDecoder.decode(name, "utf-8");
			String csname = (String) jsonObject.get("csname");
			String selectVal0 = (String) devicetype.get("selectVal0");
			log.info(selectVal0 + "!!!!!!!");
			String selectVal1 = (String) devicetype.get("selectVal1");
			if (starttime != "" && endtime != "") {
				if (selectVal0.equals("") && selectVal1.equals("")) {
					if (starttime.equals(endtime)) {
						if (csname.equals("") || csname.indexOf(",") == -1) {
							sql = "select t.* from t_pms_defect t where t.name='"
									+ namestr
									+ "' and t.discoverytime  like '"
									+ starttime
									+ "%' and  t.voltage='500kV' order by t.discoverytime desc ";
						} else {
							sql = "select t.* from t_pms_defect t where t.manufacturer ='"
									+ namestr
									+ "' and  t.discoverytime  like '"
									+ starttime
									+ "%' and  t.voltage='500kV' order by t.discoverytime desc ";
						}

					} else {

						if (csname.equals("") || csname.indexOf(",") == -1) {
							sql = "select t.* from t_pms_defect t where t.name='"
									+ namestr
									+ "' and t.discoverytime  between '"
									+ starttime
									+ "' and '"
									+ endtime
									+ "' and  t.voltage='500kV' order by t.discoverytime desc ";
						} else {
							sql = "select t.* from t_pms_defect t where t.manufacturer ='"
									+ namestr
									+ "' and  t.discoverytime  between '"
									+ starttime
									+ "' and '"
									+ endtime
									+ "' and t.voltage='500kV' order by t.discoverytime desc ";
						}

					}
				} else if (selectVal0.equals("按一次设备查询")
						&& selectVal1.equals("")) {
					if (starttime.equals(endtime)) {
						if (csname.equals("") || csname.indexOf(",") == -1) {
							sql = "select t.* from t_pms_defect t  where t.name='"
									+ namestr
									+ "' and t.voltage='500kV' and (t.deviceclass like '%发电机%' or t.deviceclass like '%变压器%' or t.deviceclass like '%电动机%' or "
									+ "t.deviceclass like '%断路器%' or t.deviceclass like '%熔断器%'  or "
									+ " t.deviceclass like '%互感器%' or t.deviceclass like '%母线' or t.deviceclass like '%电抗器%' or t.deviceclass like '%避雷器%')"
									+ " and t.discoverytime   like '"
									+ starttime
									+ "%' order by t.discoverytime desc";
						} else {
							sql = "select t.* from t_pms_defect t  where t.manufacturer ='"
									+ namestr
									+ "' and t.voltage='500kV' and (t.deviceclass like '%发电机%' or t.deviceclass like '%变压器%' or t.deviceclass like '%电动机%' or "
									+ "t.deviceclass like '%断路器%' or t.deviceclass like '%熔断器%' or "
									+ " t.deviceclass like '%互感器%' or t.deviceclass like '%母线' or t.deviceclass like '%电抗器%' or t.deviceclass like '%避雷器%' )"
									+ " and t.discoverytime   like '"
									+ starttime
									+ "%' order by t.discoverytime desc";
						}

					} else {
						if (csname.equals("") || csname.indexOf(",") == -1) {
							sql = "select t.* from t_pms_defect t where  t.name='"
									+ namestr
									+ "' and where t.voltage='500kV' and (t.deviceclass like '%发电机%' or t.deviceclass like '%变压器%' or t.deviceclass like '%电动机%' or "
									+ "t.deviceclass like '%断路器%' or t.deviceclass like '%熔断器%'  or "
									+ " t.deviceclass like '%互感器%' or t.deviceclass like '%母线' or t.deviceclass like '%电抗器%' or t.deviceclass like '%避雷器%' )"
									+ " and t.discoverytime   between '"
									+ starttime
									+ "' and '"
									+ endtime
									+ "'  order by t.discoverytime desc";

						} else {
							sql = "select t.* from t_pms_defect t  where t.manufacturer='"
									+ namestr
									+ "'  and t.voltage='500kV' and (t.deviceclass like '%发电机%' or t.deviceclass like '%变压器%' or t.deviceclass like '%电动机%' or "
									+ "t.deviceclass like '%断路器%' or t.deviceclass like '%熔断器%' or "
									+ " t.deviceclass like '%互感器%' or t.deviceclass like '%母线' or t.deviceclass like '%电抗器%' or t.deviceclass like '%避雷器%' )"
									+ " and t.discoverytime   between '"
									+ starttime
									+ "' and '"
									+ endtime
									+ "' order by t.discoverytime desc";

						}
					}
				} else if (selectVal0.equals("按二次设备查询")
						&& selectVal1.equals("")) {
					if (starttime.equals(endtime)) {
						if (csname.equals("") || csname.indexOf(",") == -1) {
							sql = "select t.* from t_pms_defect t  where t.name='"
									+ namestr
									+ "' and t.voltage='500kV' and (t.deviceclass like '%监控%' or t.deviceclass like '%保护%' or t.deviceclass like '%辅助%' or t.deviceclass like '%监测%' or t.deviceclass like '%控制%')"
									+ " and t.discoverytime   like '"
									+ starttime
									+ "%' order by t.discoverytime desc";
						} else {
							sql = "select t.* from t_pms_defect t  where t.manufacturer ='"
									+ namestr
									+ "' and t.voltage='500kV' and (t.deviceclass like '%监控%' or t.deviceclass like '%保护%' or t.deviceclass like '%辅助%' or t.deviceclass like '%监测%' or t.deviceclass like '%控制%')"
									+ " and t.discoverytime   like '"
									+ starttime
									+ "%'  order by t.discoverytime desc";
						}

					} else {

						if (csname.equals("") || csname.indexOf(",") == -1) {
							sql = "select t.* from t_pms_defect t  where t.name='"
									+ namestr
									+ "' and t.voltage='500kV' and (t.deviceclass like '%监控%' or t.deviceclass like '%保护%' or t.deviceclass like '%辅助%' or t.deviceclass like '%监测%' or t.deviceclass like '%控制%')"
									+ " and t.discoverytime   between '"
									+ starttime
									+ "' and '"
									+ endtime
									+ "' order by t.discoverytime desc";
						} else {
							sql = "select t.* from t_pms_defect t  where t.manufacturer ='"
									+ namestr
									+ "' and t.voltage='500kV' and (t.deviceclass like '%监控%' or t.deviceclass like '%保护%' or t.deviceclass like '%辅助%' or t.deviceclass like '%监测%' or t.deviceclass like '%控制%')"
									+ " and t.discoverytime   between '"
									+ starttime
									+ "' and '"
									+ endtime
									+ "'  order by t.discoverytime desc";

						}

					}
				} else if (selectVal0.equals("按一次设备查询")
						&& selectVal1.equals("按二次设备查询")) {
					if (starttime.equals(endtime)) {
						if (csname.equals("") || csname.indexOf(",") == -1) {
							sql = "select t.* from t_pms_defect t  where t.name='"
									+ namestr
									+ "' and t.voltage='500kV' and t.discoverytime  like '"
									+ starttime
									+ "%'   order by t.discoverytime desc";
						} else {
							sql = "select t.* from t_pms_defect t  where t.manufacturer ='"
									+ namestr
									+ "' and t.voltage='500kV' and t.discoverytime  like '"
									+ starttime
									+ "%'  order by t.discoverytime desc";

						}
					} else {
						if (csname.equals("") || csname.indexOf(",") == -1) {
							sql = "select t.* from t_pms_defect t  where t.name='"
									+ namestr
									+ "' and  t.voltage='500kV' and t.discoverytime  between '"
									+ starttime
									+ "' and '"
									+ endtime
									+ "'  order by t.discoverytime desc";
						} else {
							sql = "select t.* from t_pms_defect t  where t.manufacturer ='"
									+ namestr
									+ "' and  t.voltage='500kV' and t.discoverytime  between '"
									+ starttime
									+ "' and '"
									+ endtime
									+ "'  order by t.discoverytime desc";

						}
					}
				}
			} else {
				if (selectVal0.equals("按一次设备查询") && selectVal1.equals("")) {
					if (csname.equals("") || csname.indexOf(",") == -1) {
						sql = "select t.* from t_pms_defect t  where t.name='"
								+ namestr
								+ "' and t.voltage='500kV' and (t.deviceclass like '%发电机%' or t.deviceclass like '%变压器%' or t.deviceclass like '%电动机%' or "
								+ "t.deviceclass like '%断路器%' or t.deviceclass like '%熔断器%' or t.deviceclass like '%负荷开关%' or t.deviceclass like '%隔离开关%' or t.deviceclass like '%导线%' or t.deviceclass like '%绝缘子%' or "
								+ "t.deviceclass like '%电缆%' or t.deviceclass like '%电压%' or t.deviceclass like '%互感器%' or t.deviceclass like '%母线' or t.deviceclass like '%电抗器%' or t.deviceclass like '%避雷器%' or t.deviceclass like '%金具%')"
								+ " order by t.discoverytime desc";
					} else {
						sql = "select t.* from t_pms_defect t  where t.manufacturer ='"
								+ namestr
								+ "' and t.voltage='500kV' and (t.deviceclass like '%发电机%' or t.deviceclass like '%变压器%' or t.deviceclass like '%电动机%' or "
								+ "t.deviceclass like '%断路器%' or t.deviceclass like '%熔断器%' or t.deviceclass like '%负荷开关%' or t.deviceclass like '%隔离开关%' or t.deviceclass like '%导线%' or t.deviceclass like '%绝缘子%' or "
								+ "t.deviceclass like '%电缆%' or t.deviceclass like '%电压%' or t.deviceclass like '%互感器%' or t.deviceclass like '%母线' or t.deviceclass like '%电抗器%' or t.deviceclass like '%避雷器%' or t.deviceclass like '%金具%')"
								+ " order by t.discoverytime desc";
					}

				} else if (selectVal0.equals("按二次设备查询")
						&& selectVal1.equals("")) {
					if (csname.equals("") || csname.indexOf(",") == -1) {
						sql = "select t.* from t_pms_defect t  where t.name='"
								+ namestr
								+ "' and  t.voltage='500kV' and (t.deviceclass like '%监控%' or t.deviceclass like '%保护%' or t.deviceclass like '%辅助%' or t.deviceclass like '%监测%' or t.deviceclass like '%控制%'"
								+ " order by t.discoverytime desc";
					} else {
						sql = "select t.* from t_pms_defect t  where t.manufacturer='"
								+ namestr
								+ "' and t.voltage='500kV' and (t.deviceclass like '%监控%' or t.deviceclass like '%保护%' or t.deviceclass like '%辅助%' or t.deviceclass like '%监测%' or t.deviceclass like '%控制%'"
								+ " order by t.discoverytime desc";
					}

				} else {
					if (csname.equals("") && csname.indexOf(",") == -1) {
						sql = "select t.* from t_pms_defect t  where t.name='"
								+ namestr
								+ "' and t.voltage='500kV'  order by t.discoverytime desc";
					} else {
						sql = "select t.* from t_pms_defect t  where t.manufacturer='"
								+ namestr
								+ "' and t.voltage='500kV'  order by t.discoverytime desc";
					}

				}

			}

			List<?> obs = dao.nativeQuery(sql);
			//log.info(obs);
			return JSONObject.toJSONString(obs);
		} catch (UnsupportedEncodingException e) {

			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 根据选择条件获取缺陷记录
	 * 
	 * @param datetime
	 * @return
	 */

	@GetMapping("/getPmsDefectsBySelection/{option}")
	public String getPmsDefectsBySelection(@PathVariable("option") String option) {
		String sql = "select t.* from t_pms_defect t ";
		String one = "t.voltage='500kV' and (t.deviceclass like '%发电机%' or t.deviceclass like '%变压器%' or t.deviceclass like '%电动机%' or "
				+ "t.deviceclass like '%断路器%' or t.deviceclass like '%熔断器%' or "
				+ "t.deviceclass like '%互感器%' or t.deviceclass like '%母线' or t.deviceclass like '%电抗器%' or t.deviceclass like '%避雷器%')";
		String two = "t.voltage='500kV' and (t.deviceclass like '%监控%' or t.deviceclass like '%保护%' or t.deviceclass like '%辅助%' or t.deviceclass like '%监测%' or t.deviceclass like '%控制%')";
		String str = "";
		JSONObject jsonObject = JSON.parseObject(option);
		JSONObject devicetype = (JSONObject) jsonObject.get("devicetype");
		JSONObject datetimeJson = (JSONObject) jsonObject.get("datetime");
		String csname = (String) jsonObject.get("csname");
		String[] strs = csname.split(",");
		for (int i = 0; i < strs.length; i++) {
			str = str + "'" + strs[i] + "',";
		}
		str = str.substring(0, str.length() - 1);
		String starttime = (String) datetimeJson.get("starttime");
		String endtime = (String) datetimeJson.get("endtime");
		String selectVal0 = (String) devicetype.get("selectVal0");
		String selectVal1 = (String) devicetype.get("selectVal1");
//		log.info(starttime);
//		log.info(endtime);
//		log.info(selectVal0);
//		log.info(selectVal1);
		if (!starttime.equals("") && !endtime.equals("")) {
			if (selectVal0.equals("") && selectVal1.equals("")) {

				if (csname.equals("")) {
					sql += "where  t.discoverytime  between '"
							+ starttime
							+ "' and '"
							+ endtime
							+ "' and  t.voltage='500kV' order by t.discoverytime desc ";
				} else {
					sql += "where t.manufacturer in ("
							+ str
							+ ") and  t.discoverytime  between '"
							+ starttime
							+ "' and '"
							+ endtime
							+ "' and  t.voltage='500kV' order by t.manufacturer";

				}
			} else if (selectVal0.equals("按一次设备查询") && selectVal1.equals("")) {
				if (csname.equals("")) {
					sql += "where " + one + " and t.discoverytime   between '"
							+ starttime + "' and '" + endtime
							+ "' order by t.discoverytime";
				} else {

					sql += "where t.manufacturer in (" + str + ") and " + one
							+ " and t.discoverytime   between '" + starttime
							+ "' and '" + endtime + "' order by t.manufacturer";

				}

			} else if (selectVal0.equals("按二次设备查询") && selectVal1.equals("")) {
				if (csname.equals("")) {
					sql += "where " + two + " and t.discoverytime   between '"
							+ starttime + "' and '" + endtime
							+ "' order by t.discoverytime";
				} else {
					sql += "where t.manufacturer in (" + str + ") and " + two
							+ " and t.discoverytime   between '" + starttime
							+ "' and '" + endtime
							+ "' order by t.manufacturer ";

				}

			} else if (selectVal0.equals("按一次设备查询")
					&& selectVal1.equals("按二次设备查询")) {
				if (csname.equals("")) {
					sql += "where  t.voltage='500kV' and t.discoverytime  between '"
							+ starttime
							+ "' and '"
							+ endtime
							+ "' order by t.discoverytime";
				} else {

					sql += "where t.manufacturer in ("
							+ str
							+ ") and  t.voltage='500kV' and t.discoverytime  between '"
							+ starttime + "' and '" + endtime
							+ "'  order by t.manufacturer ";

				}
			}

		} else {
			if (selectVal0.equals("按一次设备查询") && selectVal1.equals("")) {
				log.info("进来了！！！");
				if (csname.equals("")) {
					sql += "where " + one + " order by t.discoverytime";

				} else {
					sql += "where t.manufacturer in (" + str + ") and " + one
							+ " order by t.manufacturer ";

				}
			} else if (selectVal0.equals("按二次设备查询") && selectVal1.equals("")) {
				if (csname.equals("")) {
					sql += "where " + two + " order by t.discoverytime";
				} else {

					sql += "where t.manufacturer in (" + str + ") and " + two
							+ " order by t.manufacturer";

				}

			} else {
				if (csname.equals("")) {
					sql += "where t.voltage='500kV' order by t.discoverytime ";
				} else {
					sql += "where t.manufacturer ='" + str
							+ "' and t.voltage='500kV' order by t.manufacturer";

				}

			}

		}
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);

	}

	/**
	 * 获取某一时间发现的缺陷记录
	 * 
	 * @param datetime
	 * @return
	 */

	@GetMapping("/getPmsDefectsByDate/{datetime}")
	public String getPmsDefectsByDate(@PathVariable("datetime") String datetime) {
		String sql = "select t.defect_id,t.name,t.deviceclass,t.description,t.status,t.discoveryteam,t.discoverytime from t_pms_defect t where t.discoverytime like '"
				+ datetime
				+ "%' and voltage='500kV' order by t.discoverytime desc";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 获取在某一时间段内已经完成的定检
	 * 
	 * @param starttime
	 * @param endtime
	 * @return
	 */

	@GetMapping("/getPmsScheduledMaintenanceByDate/{starttime}/{endtime}")
	public String getPmsScheduledMaintenanceByDate(
			@PathVariable("starttime") String starttime,
			@PathVariable("endtime") String endtime) {
		String sql = "select t.defect_id,t.name,t.deviceclass,t.description,t.status,t.discoveryteam,t.discoverytime from t_pms_scheduled_maintenance t where  t.actual_endtime  between '"
				+ starttime + "' and '" + endtime + "'";
		List<?> obs = dao.nativeQuery(sql);
		log.info(JSONObject.toJSONString(obs));
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 查询所有的生产类定检计划
	 * 
	 * @return
	 */

	@GetMapping("/getPmsScheduledMaintenance")
	public String getPmsScheduledMaintenance() {
		String sql = "select t.scheme_id,t.scheme_type,t.scheme_property,t.work_type,t.work_team,t.work_place,t.work_content, t.principal,t.state_plan from t_pms_scheduled_maintenance t";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 获取某一时间段内完成的维护检修记录
	 * 
	 * @param starttime
	 * @param endtime
	 * @return
	 */

	@GetMapping("/getPmsPerambulationByDate/{starttime}/{endtime}")
	public String getPmsPerambulationByDate(
			@PathVariable("starttime") String starttime,
			@PathVariable("endtime") String endtime) {
		String sql = "select t.plan_id,t.plan_type,t.plan_property,t.work_type,t.work_place,t.principal,t.work_department from t_pms_perambulation t where t.actual_endtime  between '"
				+ starttime + "' and '" + endtime + "'";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 
	 * 获取所有缺陷设备生产厂家
	 * 
	 * @return
	 */

	@GetMapping("/getAllManufacturer")
	public String getAllManufacturer() {
		String sql = "select distinct t.manufacturer  from t_pms_defect t where t.voltage='500kV' and t.manufacturer is not null ";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 根据厂家名称检索该厂家的缺陷设备
	 * 
	 * @param manufacturerName
	 * @return
	 */
	@GetMapping("/getPmsDefectsByManufacturer/{nameStr}")
	public String getPmsDefectsByManufacturer(
			@PathVariable("nameStr") String nameStr) {
		String sql = "select t.defect_id,t.position,t.description,t.status, t.name, t.unittype from t_pms_defect t where t.voltage='500kV' and t.manufacturer='"
				+ nameStr + "'";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);

	}

	/**
	 * 同一厂家的同一型号设备缺陷数量汇总
	 * 
	 * @return
	 */

	@GetMapping("/getPmsDefectsGroup")
	public String getPmsDefectsGroup() {
		String sql = "select t.manufacturer,t.unittype,count(*) from t_pms_defect t where voltage='500kV' group by t.manufacturer ,t.unittype ";
		List<?> obs = dao.nativeQuery(sql);
		log.info(JSONObject.toJSONString(obs));
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 按照厂家和设备型号的缺陷汇总
	 * 
	 * @param flag
	 * @return
	 */

	@GetMapping("/getPmsDefectsCount/{flag}")
	public String getPmsDefectsCount(@PathVariable("flag") String flag) {
		String sql = "";
		if (flag.equals("m")) {
			sql = "select t.manufacturer,count(*)  from t_pms_defect t where t.voltage='500kV' group by t.manufacturer order by count(*) desc";
		} else {

			sql = "select t.unittype,count(*) from t_pms_defect t where t.voltage='500kV' group by t.unittype order by count(*) desc";
		}
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 根据设备型号检索生产该设备的厂家
	 * 
	 * @param manufacturerName
	 * @return
	 */

	@GetMapping("/getPmsDefectsByUnittype/{unittypeStr}")
	public String getPmsDefectsByUnittype(
			@PathVariable("unittypeStr") String unittypeStr) {
		String sql = "select t.unittype, t.name, t.deviceclass, t.manufacturer,t.dateproduction,t.dateput from t_pms_defect t where t.voltage='500kV' and t.unittype='"
				+ unittypeStr + "'";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 根据生产类定检类的不同状态的数据分析
	 * 
	 * @return
	 */

	@GetMapping("/getPmsScheduledMaintenanceCount")
	public String getPmsScheduledMaintenanceCount() {
		String sql = "select count(t.state_plan='已完成' or null),count(t.state_plan='待确认' or null),count(t.state_plan='执行中' or null),count(t.state_plan='已取消' or null),count(t.state_plan='待发布' or null),count(t.state_plan='未派工' or null),count(t.state_plan='新增' or null) from t_pms_scheduled_maintenance as t";
		List<?> obs = dao.nativeQuery(sql);
		JSONObject.toJSONString(obs);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 根据生产类定检计划状态汇总该状态下的所有数据
	 * 
	 * @param statePlan
	 * @return
	 */

	@GetMapping("/getPmsScheduledMaintenanceByStatus/{statePlan}")
	public String getPmsScheduledMaintenanceByStatus(
			@PathVariable("statePlan") String statePlan) {
		String sql = "select t.scheme_id,t.scheme_type,t.scheme_property,t.work_type,t.work_team,t.work_place,t.work_content, t.principal,t.state_plan from t_pms_scheduled_maintenance t where t.state_plan='"
				+ statePlan + "'";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 根据计划管理的不同状态的数据分析
	 * 
	 * @return
	 */

	@GetMapping("/getPmsPlanCount")
	public String getPmsPlanCount() {
		String sql = "select count(t.state_plan='已完成' or null),count(t.state_plan='待确认' or null),count(t.state_plan='执行中' or null),count(t.state_plan='已取消' or null),count(t.state_plan='待发布' or null),count(t.state_plan='未派工' or null),count(t.state_plan='新增' or null) from t_pms_plan as t";
		List<?> obs = dao.nativeQuery(sql);
		JSONObject.toJSONString(obs);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 根据计划管理的状态汇总该状态下的所有数据
	 * 
	 * @param statePlan
	 * @return
	 */

	@GetMapping("/getPmsPlanByStatus/{statePlan}")
	public String getPmsPlanByStatus(@PathVariable("statePlan") String statePlan) {
		String sql = "select t.scheme_id,t.scheme_type,t.scheme_property,t.work_type,t.work_team,t.work_place,t.work_content, t.principal,t.state_plan from t_pms_scheduled_maintenance t where t.state_plan='"
				+ statePlan + "'";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 查询一次设备
	 * 
	 * @return
	 */

	@GetMapping("/getPrimaryEquipment")
	public String getPrimaryEquipment() {
		String sql = "select t.name,count(*) from t_pms_defect t  where t.voltage='500kV' and (t.deviceclass like '%发电机%' or t.deviceclass like '%变压器%' or t.deviceclass like '%电动机%' or "
				+ "t.deviceclass like '%断路器%' or t.deviceclass like '%熔断器%' or t.deviceclass like '%负荷开关%' or t.deviceclass like '%隔离开关%' or t.deviceclass like '%导线%' or t.deviceclass like '%绝缘子%' or "
				+ "t.deviceclass like '%电缆%' or t.deviceclass like '%电压%' or t.deviceclass like '%互感器%' or t.deviceclass like '%母线' or t.deviceclass like '%电抗器%' or t.deviceclass like '%避雷器%' or t.deviceclass like '%金具%')"
				+ " group by t.name order by count(*) desc";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 通过名称获取该设备名称下的所有的一次设备
	 * 
	 * @param name
	 * @return
	 */

	@GetMapping("/getPrimaryEquipmentByName/{name}")
	public String getPrimaryEquipmentByName(@PathVariable("name") String name) {
		String sql = "select t.defect_id,t.position,t.description,t.status, t.name, t.unittype from t_pms_defect t where t.voltage='500kV' and t.name='"
				+ name + "'";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 查询二次设备
	 * 
	 * @return
	 */

	@GetMapping("/getSecondaryEquipment")
	public String getSecondaryEquipment() {
		String sql = "select t.name,count(*) from t_pms_defect t  where t.voltage='500kV' and (t.deviceclass like '%监控%' or t.deviceclass like '%保护%'"
				+ " group by t.name order by count(*) desc";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 根据条件统计跳闸信息
	 * 
	 * @param option
	 * @return
	 */

	@GetMapping("/getTrip/{option}")
	public String getTrip(@PathVariable("option") String option) {
		JSONObject jsonObject = JSON.parseObject(option);
		String subname = (String) jsonObject.get("subname");
		String endtime = (String) jsonObject.get("datetime");
		String starttime = "";
		String sql = "select t.sub_name,t.name,date_format(t.last_tripatime,'%Y-%m-%d %h:%m:%s')"
				+ ",date_format(t.last_tripbtime,'%Y-%m-%d %h:%m:%s'),"
				+ "date_format(t.last_tripctime,'%Y-%m-%d %h:%m:%s') from t_cim_breaker t ";
		try {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Date date = sdf.parse(endtime);
			DateTime dateTime = new DateTime(date);
			starttime = dateTime.plusYears(-1).toString("yyyy-MM-dd");
			if (!subname.equals("")) {
				sql += " where t.sub_name='"
						+ subname
						+ "' and t.vl_id in (select id from t_cim_voltagelevel t2 where  t2.baseVoltage in( select id from t_cim_basevoltage t where t.nominalVoltage > 499)) and (t.last_tripatime < '"
						+ starttime + "' or  t.last_tripbtime < '" + starttime
						+ "' or  t.last_tripctime < '" + starttime + "')";

			} else {
				sql += " where  t.vl_id in (select id from t_cim_voltagelevel t2 where  t2.baseVoltage in( select id from t_cim_basevoltage t where t.nominalVoltage > 499)) and (t.last_tripatime < '"
						+ starttime
						+ "' or  t.last_tripbtime < '"
						+ starttime
						+ "' or  t.last_tripctime < '" + starttime + "')";
			}
			List<?> obs = dao.nativeQuery(sql);
			return JSONObject.toJSONString(obs);
		} catch (ParseException e) {

			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 获取站点内的所有跳闸信息
	 * 
	 * @param option
	 * @return
	 */

	@GetMapping("/getTripByStation/{subid}")
	public String getTripByStation(@PathVariable("subid") String subid) {
		Calendar calendar=Calendar.getInstance();
		int currentYear = calendar.get(Calendar.YEAR);// 当前年份
		String sql = "select t.name,date_format(t.last_trip_a_time,'%Y-%m-%d %h:%m:%s')"
				+ ",date_format(t.last_trip_b_time,'%Y-%m-%d %h:%m:%s'),"
				+ "date_format(t.last_trip_c_time,'%Y-%m-%d %h:%m:%s') from t_special_maintenance_station_breakers t where t.station_id='"
				+ subid + "' and year="+currentYear;

		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 获取breaker 表中的所有站点
	 */

	@GetMapping("/getAllStation")
	public String getAllStation() {
		String sql = "select distinct t.sub_name from t_cim_breaker t";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 按照单位名称分组，分析单位下缺陷设备的数量
	 * 
	 * @return
	 */

	@GetMapping("/getUnitname")
	public String getUnitname() {
		String sql = "select t.unitname, count(*) from t_pms_defect t group by t.unitname order by count(*) desc";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}


	@GetMapping("/getContents")
	public String getContents() {
		String str = "小龙潭电厂,以礼河三级";
		String strs[] = str.split(",");
		String s = "";
		for (int i = 0; i < strs.length; i++) {
			s = s + "'" + strs[i] + "',";
		}
		log.info(s.substring(0, s.length() - 1));
		// String sql
		// ="select t.name,t.sub_name,t.last_triptime from t_cim_breaker t where t.sub_name in ("+str+")";
		// List<?> obs=dao.nativeQuery(sql);
		return s.substring(0, s.length() - 1);
	}

	/**
	 * 获取某一站点的所有断路器
	 * 
	 * @return
	 */

	@GetMapping("/getBreakerDrive")
	public String getBreakerDrive() {
		String jsonStr = "[{'name':'5011','value':[{'name':'跳闸A','time':'2016-06-01','num':5},{'name':'跳闸B','time':'2016-07-01','num':3},{'name':'跳闸C','time':'2016-08-01','num':2}]},"
				+ "{'name':'5012','value':[{'name':'跳闸A','time':'2016-04-21','num':3},{'name':'跳闸B','time':'2016-07-01','num':5},{'name':'跳闸C','time':'2016-08-01','num':3}]},"
				+ "{'name':'5013','value':[{'name':'跳闸A','time':'2016-06-01','num':2},{'name':'跳闸B','time':'2016-07-01','num':2},{'name':'跳闸C','time':'2016-08-01','num':4}]},"
				+ "{'name':'5014','value':[{'name':'跳闸A','time':'2016-06-01','num':1},{'name':'跳闸B','time':'2016-07-01','num':1},{'name':'跳闸C','time':'2016-08-01','num':2}]},"
				+ "{'name':'5024','value':[{'name':'跳闸A','time':'2016-06-01','num':3},{'name':'跳闸B','time':'2016-07-01','num':4},{'name':'跳闸C','time':'2016-08-01','num':1}]},"
				+ "{'name':'5022','value':[{'name':'跳闸A','time':'2016-06-01','num':4},{'name':'跳闸B','time':'2016-07-01','num':3},{'name':'跳闸C','time':'2016-08-01','num':5}]},"
				+ "{'name':'5021','value':[{'name':'跳闸A','time':'2016-06-01','num':2},{'name':'跳闸B','time':'2016-07-01','num':5},{'name':'跳闸C','time':'2016-08-01','num':3}]},"
				+ "{'name':'5034','value':[{'name':'跳闸A','time':'2016-06-01','num':1},{'name':'跳闸B','time':'2016-07-01','num':1},{'name':'跳闸C','time':'2016-08-01','num':5}]}]";
		return JSONObject.parse(jsonStr).toString();
	}

	/**
	 * 获取某一站点所有的未传动断路器
	 * 
	 * @return
	 */

	@GetMapping("/getBreakerFenxi/{subid}")
	public String getBreakerFenxi(@PathVariable("subid") String subid) {
		// String
		// jsonStr="{'total':8,'finish':4,'unfinish':4,value:[{'name':'5011','value':[{'name':'跳闸A','time':'2016-06-01','num':5,'state':1},{'name':'跳闸B','time':'2016-05-10','num':3,'state':0},{'name':'跳闸C','time':'2016-08-01','num':2,'state':1}]},"
		// +
		// "{'name':'5012','value':[{'name':'跳闸A','time':'2016-04-21','num':3,'state':0},{'name':'跳闸B','time':'2016-07-10','num':5,'state':1},{'name':'跳闸C','time':'2016-04-15','num':3,'state':0}]},"
		// +
		// "{'name':'5013','value':[{'name':'跳闸A','time':'2016-06-21','num':2,'state':1},{'name':'跳闸B','time':'2016-03-01','num':2,'state':0},{'name':'跳闸C','time':'2016-08-20','num':4,'state':1}]},"
		// +
		// "{'name':'5014','value':[{'name':'跳闸A','time':'2016-05-01','num':1,'state':0},{'name':'跳闸B','time':'2016-06-01','num':1,'state':1},{'name':'跳闸C','time':'2016-10-01','num':2,'state':1}]}]}";
		// return JSONObject.parse(jsonStr).toString();
		String sql = "select t.name,date_format(t.last_trip_a_time,'%Y-%m-%d %h:%m:%s'),date_format(t.last_trip_b_time,'%Y-%m-%d %h:%m:%s'),date_format(t.last_trip_c_time,'%Y-%m-%d %h:%m:%s') from t_special_maintenance_station_breakers t where t.station_id='"
				+ subid + "'";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 获取所有断路器
	 * 
	 * @return
	 */

	@GetMapping("/getAllBreakerDrive")
	public String getAllBreakerDrive() {
		String jsonStr = "[{'name':'5011','value':[{'name':'跳闸A','time':'2016-06-01','num':5},{'name':'跳闸B','time':'2016-07-01','num':3},{'name':'跳闸C','time':'2016-08-01','num':2}]},"
				+ "{'name':'5012','value':[{'name':'跳闸A','time':'2016-04-21','num':3},{'name':'跳闸B','time':'2016-07-01','num':5},{'name':'跳闸C','time':'2016-08-01','num':3}]},"
				+ "{'name':'5013','value':[{'name':'跳闸A','time':'2016-06-01','num':2},{'name':'跳闸B','time':'2016-07-01','num':2},{'name':'跳闸C','time':'2016-08-01','num':4}]},"
				+ "{'name':'5014','value':[{'name':'跳闸A','time':'2016-06-01','num':1},{'name':'跳闸B','time':'2016-07-01','num':1},{'name':'跳闸C','time':'2016-08-01','num':2}]},"
				+ "{'name':'5024','value':[{'name':'跳闸A','time':'2016-06-01','num':3},{'name':'跳闸B','time':'2016-07-01','num':4},{'name':'跳闸C','time':'2016-08-01','num':1}]},"
				+ "{'name':'5022','value':[{'name':'跳闸A','time':'2016-06-01','num':4},{'name':'跳闸B','time':'2016-07-01','num':3},{'name':'跳闸C','time':'2016-08-01','num':5}]},"
				+ "{'name':'5021','value':[{'name':'跳闸A','time':'2016-06-01','num':2},{'name':'跳闸B','time':'2016-07-01','num':5},{'name':'跳闸C','time':'2016-08-01','num':3}]},"
				+ "{'name':'5012','value':[{'name':'跳闸A','time':'2016-04-21','num':3},{'name':'跳闸B','time':'2016-07-01','num':5},{'name':'跳闸C','time':'2016-08-01','num':3}]},"
				+ "{'name':'5023','value':[{'name':'跳闸A','time':'2016-06-01','num':2},{'name':'跳闸B','time':'2016-07-01','num':2},{'name':'跳闸C','time':'2016-08-01','num':4}]},"
				+ "{'name':'5054','value':[{'name':'跳闸A','time':'2016-06-01','num':1},{'name':'跳闸B','time':'2016-07-01','num':1},{'name':'跳闸C','time':'2016-08-01','num':2}]},"
				+ "{'name':'5064','value':[{'name':'跳闸A','time':'2016-06-01','num':3},{'name':'跳闸B','time':'2016-07-01','num':4},{'name':'跳闸C','time':'2016-08-01','num':1}]},"
				+ "{'name':'5023','value':[{'name':'跳闸A','time':'2016-06-01','num':4},{'name':'跳闸B','time':'2016-07-01','num':3},{'name':'跳闸C','time':'2016-08-01','num':5}]},"
				+ "{'name':'5020','value':[{'name':'跳闸A','time':'2016-06-01','num':2},{'name':'跳闸B','time':'2016-07-01','num':5},{'name':'跳闸C','time':'2016-08-01','num':3}]},"
				+ "{'name':'5042','value':[{'name':'跳闸A','time':'2016-04-21','num':3},{'name':'跳闸B','time':'2016-07-01','num':5},{'name':'跳闸C','time':'2016-08-01','num':3}]},"
				+ "{'name':'5023','value':[{'name':'跳闸A','time':'2016-06-01','num':2},{'name':'跳闸B','time':'2016-07-01','num':2},{'name':'跳闸C','time':'2016-08-01','num':4}]},"
				+ "{'name':'5014','value':[{'name':'跳闸A','time':'2016-06-01','num':1},{'name':'跳闸B','time':'2016-07-01','num':1},{'name':'跳闸C','time':'2016-08-01','num':2}]},"
				+ "{'name':'5054','value':[{'name':'跳闸A','time':'2016-06-01','num':3},{'name':'跳闸B','time':'2016-07-01','num':4},{'name':'跳闸C','time':'2016-08-01','num':1}]},"
				+ "{'name':'5082','value':[{'name':'跳闸A','time':'2016-06-01','num':4},{'name':'跳闸B','time':'2016-07-01','num':3},{'name':'跳闸C','time':'2016-08-01','num':5}]},"
				+ "{'name':'5021','value':[{'name':'跳闸A','time':'2016-06-01','num':2},{'name':'跳闸B','time':'2016-07-01','num':5},{'name':'跳闸C','time':'2016-08-01','num':3}]},"
				+ "{'name':'5062','value':[{'name':'跳闸A','time':'2016-04-21','num':3},{'name':'跳闸B','time':'2016-07-01','num':5},{'name':'跳闸C','time':'2016-08-01','num':3}]},"
				+ "{'name':'5083','value':[{'name':'跳闸A','time':'2016-06-01','num':2},{'name':'跳闸B','time':'2016-07-01','num':2},{'name':'跳闸C','time':'2016-08-01','num':4}]},"
				+ "{'name':'5044','value':[{'name':'跳闸A','time':'2016-06-01','num':1},{'name':'跳闸B','time':'2016-07-01','num':1},{'name':'跳闸C','time':'2016-08-01','num':2}]},"
				+ "{'name':'5024','value':[{'name':'跳闸A','time':'2016-06-01','num':3},{'name':'跳闸B','time':'2016-07-01','num':4},{'name':'跳闸C','time':'2016-08-01','num':1}]},"
				+ "{'name':'5062','value':[{'name':'跳闸A','time':'2016-06-01','num':4},{'name':'跳闸B','time':'2016-07-01','num':3},{'name':'跳闸C','time':'2016-08-01','num':5}]},"
				+ "{'name':'5051','value':[{'name':'跳闸A','time':'2016-06-01','num':2},{'name':'跳闸B','time':'2016-07-01','num':5},{'name':'跳闸C','time':'2016-08-01','num':3}]},"
				+ "{'name':'5037','value':[{'name':'跳闸A','time':'2016-06-01','num':1},{'name':'跳闸B','time':'2016-07-01','num':1},{'name':'跳闸C','time':'2016-08-01','num':5}]}]";
		return JSONObject.parse(jsonStr).toString();
	}

	/**
	 * 获取所有的未传动断路器
	 * 
	 * @return
	 */

	@GetMapping("/getAllBreakerFenxi/{endtime}")
	public String getAllBreakerFenxi(@PathVariable("endtime") String endtime) {
		String starttime = "";
		try {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Date date = sdf.parse(endtime);
			DateTime dateTime = new DateTime(date);
			starttime = dateTime.plusYears(-1).toString("yyyy-MM-dd");
		} catch (ParseException e) {

			e.printStackTrace();
		}
		String sql = "select t.sub_name,t.name,date_format(t.last_tripatime,'%Y-%m-%d %h:%m:%s')"
				+ ",date_format(t.last_tripbtime,'%Y-%m-%d %h:%m:%s'),"
				+ "date_format(t.last_tripctime,'%Y-%m-%d %h:%m:%s') from t_cim_breaker t"
				+ " where  t.vl_id in (select id from t_cim_voltagelevel t2 where  t2.baseVoltage in( select id from t_cim_basevoltage t where t.nominalVoltage > 499)) and (t.last_tripatime < '"
				+ starttime
				+ "' or  t.last_tripbtime < '"
				+ starttime
				+ "' or  t.last_tripctime < '" + starttime + "') ";

		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 获取所有断路器个数
	 * 
	 * @return
	 */

	@GetMapping("/getBreakerCount")
	public String getBreakerCount() {
		String sql = "select count(*) from t_cim_breaker t where  t.vl_id in (select id from t_cim_voltagelevel t2 where  t2.baseVoltage in( select id from t_cim_basevoltage t where t.nominalVoltage > 499))";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 获取断路器动作时间
	 * 
	 * @return
	 */

	@GetMapping("/getBreakerActTimeByVoltage/{option}")
	public String getBreakerActTimeByVoltage(@PathVariable("option") String option) {
		Random random = new Random();
		List<Float> rtn = new ArrayList<>();
		for (int i = 0; i < 200; i++) {
			rtn.add(new Float(random.nextInt(10) + 40 + random.nextFloat()));
		}
		for (int i = 0; i < 120; i++) {
			rtn.add(new Float(random.nextInt(10) + 30 + random.nextFloat()));
		}
		for (int i = 0; i < 4; i++) {
			rtn.add(new Float(random.nextInt(10) + 50 + random.nextFloat()));
		}
		return JSONObject.toJSONString(rtn);
	}

	/**
	 * 变电站特维分析
	 * 
	 * @param endtime
	 * @return
	 */

	@GetMapping("/getSpecialMaintenaceStation")
	public String getSpecialMaintenaceStation() {
		String sql = "select t1.longitude,t1.latitude, t1.name  from t_cim_substation t1 where left(t1.name,2) in ( select left(t.station,2) from t_special_maintenance_station t group by t.station)";
		List<?> obs = dao.nativeQuery(sql);
		return JSONObject.toJSONString(obs);
	}

	/**
	 * 获取短路电流数据
	 */
	@GetMapping("/getShortCircuitInfo/{option}")
	public String getShortCircuitInfo(@PathVariable("option") String option) {
		Map<String, Object> map = JSONObject.parseObject(option);
		List<BreakerActionInfoDTO> list = query.getBreakerFaultActionInfo(
				(String) map.get("breakerManufacturer"),
				(String) map.get("breakerType"), (String) map.get("startDate"),
				(String) map.get("endDate"), map.get("maxTime").toString());
		return JSONArray.fromObject(list).toString();
	}
}
